/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package IncomeExpense;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

/**
 *
 * @author Shailesh
 */
public class Query {
    static Connection con = null;
    
    public static int add(IncomeExpenseBean ib) throws SQLException {
    
    int i=0;
    
    try{
        con = DBConnection.Connection.getConnection();
        PreparedStatement pst = con.prepareStatement("insert into journal values(journalid.nextval,?,?,?,?,?,?,?,sysdate,?,?)");
        pst.setInt(1, ib.getAccountType());
        pst.setString(2, ib.getDescription());
        pst.setFloat(3, ib.getAmount());
        pst.setInt(4, ib.getPaymode());
        pst.setString(5, ib.getChequeno());
        pst.setString(6, ib.getBank());
        pst.setString(7, ib.getBranch());
        pst.setDate(8, ib.getDOT());
        pst.setInt(9, ib.getUserId());
        i = pst.executeUpdate();
    
    }catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
    
    return i;
    }
    
    
     public static Vector viewAll() throws SQLException {
        Vector<Object> list = new Vector<Object>();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select account_type,amount,pay_mode,cheque_no,bank,branch,dot,description,JOURNAL_ID from JOURNAL");

            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                IncomeExpenseBean ib = new IncomeExpenseBean();
                
                
                ib.setAccountType(rs.getInt(1));
                ib.setAmount(rs.getFloat(2));
                ib.setPaymode(rs.getInt(3));
                ib.setChequeno(rs.getString(4));
                ib.setBank(rs.getString(5));
                ib.setBranch(rs.getString(6));
                ib.setDOT(rs.getDate(7));
                ib.setDescription(rs.getString(8));
                ib.setJournalId(rs.getLong("JOURNAL_ID"));
                list.add(ib);
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }
     public static Vector viewSpecific(String col,int val) throws SQLException {
        Vector<Object> list = new Vector<Object>();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select account_type,amount,pay_mode,cheque_no,bank,branch,dot,description,JOURNAL_ID from JOURNAL where "+col+"=?");
            pre.setInt(1, val);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                IncomeExpenseBean ib = new IncomeExpenseBean();
                ib.setAccountType(rs.getInt(1));
                ib.setAmount(rs.getFloat(2));
                ib.setPaymode(rs.getInt(3));
                ib.setChequeno(rs.getString(4));
                ib.setBank(rs.getString(5));
                ib.setBranch(rs.getString(6));
                ib.setDOT(rs.getDate(7));
                ib.setDescription(rs.getString(8));
                ib.setJournalId(rs.getLong("JOURNAL_ID"));
                list.add(ib);
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }
    
     
     
      public static int edit(IncomeExpenseBean ib) throws SQLException {
    
    int i=0;
    
    try{
        con = DBConnection.Connection.getConnection();
        PreparedStatement pst = con.prepareStatement("update  journal set ACCOUNT_TYPE=?,DESCRIPTION=?,AMOUNT=?,PAY_MODE=?,CHEQUE_NO=?,BANK=?,BRANCH=?,ENTRY_DATE=sysdate,DOT=?,user_id=? where JOURNAL_ID=?");
        pst.setInt(1, ib.getAccountType());
        pst.setString(2, ib.getDescription());
        pst.setFloat(3, ib.getAmount());
        pst.setInt(4, ib.getPaymode());
        pst.setString(5, ib.getChequeno());
        pst.setString(6, ib.getBank());
        pst.setString(7, ib.getBranch());
        pst.setDate(8, ib.getDOT());
        pst.setInt(9, ib.getUserId());
        pst.setLong(10, ib.getJournalId());
        i = pst.executeUpdate();
    
    }catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
    
    return i;
    }
   
}
